package com.cma.cmabackend.util;

import com.cma.cmabackend.entity.Equipment;
import com.cma.cmabackend.entity.Parameter;
import com.cma.cmabackend.entity.Project;
import com.cma.cmabackend.entity.Standard;
import com.cma.cmabackend.service.CategoryService;
import com.cma.cmabackend.service.TypeService;
import jakarta.annotation.Resource;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import cn.hutool.core.convert.NumberChineseFormatter;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;


@Component
public class FileProcess {
     class FormData {
        Standard standard;
        Parameter parameter;
        Integer categoryid;
        String categoryname;
        Integer typeid;
        String typename;
        Equipment equipment;
    }

    @Resource
    CategoryService categoryService;
    @Resource
    TypeService typeService;

    private static void setCellBorder(Sheet sheet, CellRangeAddress region) {
        RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
    }

    static public String save(MultipartFile file) {
        String uuid = UUID.randomUUID().toString();
        String originalFilename = file.getOriginalFilename();
        String fileExtension = StringUtils.getFilenameExtension(originalFilename);
        String newFilename = uuid + "." + fileExtension;
        File filePath = new File("resources/files", newFilename);
        if (!filePath.getParentFile().exists()) {
            filePath.getParentFile().mkdirs();
        }

        try {
            file.transferTo(filePath.getAbsoluteFile());
            return newFilename;
        } catch (IOException e) {
            e.printStackTrace();
            return "";
        }
    }

    public String generateExcel1(Project project) {
        Set<Equipment> equipmentSet = new HashSet<>();

        for (Standard standard : project.getStandards()) {
            for (Parameter parameter : standard.getParameterList()) {
                equipmentSet.addAll(parameter.getEquipmentList());
            }
        }

        List<Equipment> equipmentList = new ArrayList<>(equipmentSet);

        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet("仪器设备信息表");
        // 创建单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        // 创建标题行
        Row headerRow1 = sheet.createRow(1);
        Row headerRow0 = sheet.createRow(0);
        headerRow0.createCell(0).setCellValue("仪器设备信息表");
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
        //headerRow1.createCell(0).setCellValue("ID");
        headerRow1.createCell(0).setCellValue("仪器设备编号");
        headerRow1.createCell(1).setCellValue("名称");
        headerRow1.createCell(2).setCellValue("型号规格等级");
        headerRow1.createCell(3).setCellValue("测量范围");
        headerRow1.createCell(4).setCellValue("设备购买年份");
        headerRow1.createCell(5).setCellValue("溯源方式");
        headerRow1.createCell(6).setCellValue("有效截止日期至");
        headerRow1.createCell(7).setCellValue("设备来源");

        // 填充数据
        int rowNum = 2;
        for (Equipment equipment : equipmentList) {
            Row row = sheet.createRow(rowNum++);
            //row.createCell(0).setCellValue(equipment.getId());
            row.createCell(0).setCellValue(equipment.getEquipNo());
            row.createCell(1).setCellValue(equipment.getName());
            row.createCell(2).setCellValue(equipment.getLevel());
            row.createCell(3).setCellValue(equipment.getScope());
            row.createCell(4).setCellValue(equipment.getAddYear());
            row.createCell(5).setCellValue(equipment.getTraceMethod());
            row.createCell(6).setCellValue(equipment.getValidDate());
            row.createCell(7).setCellValue(equipment.getSource());
        }

        // 绘制边框
        for (Row row : sheet) {
            for (Cell cell : row) {
                cell.setCellStyle(style);
            }
        }
        setCellBorder(sheet, new CellRangeAddress(0, 0, 0, 7));
        for (int i = 0; i <= 7; ++i) {
            setCellBorder(sheet, new CellRangeAddress(1, 2, i, i));
        }

        String uuid = UUID.randomUUID().toString();
        String newFilename = uuid + ".xlsx";
        File filePath = new File("resources/excels", newFilename);
        if (!filePath.getParentFile().exists()) {
            filePath.getParentFile().mkdirs();
        }

        // 保存Excel文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return newFilename;
    }

    public String generateExcel2(Project project) {
        Set<FormData> testingCapabilitySet = new HashSet<>();

        for (Standard standard : project.getStandards()) {
            Integer categoryId = standard.getCategoryId();
            Integer typeId = standard.getTypeId();
            String categoryName = categoryService.getCategoryNameById(categoryId);
            String typeName = typeService.getTypeNameById(typeId);

            for (Parameter parameter : standard.getParameterList()) {
                FormData tem = new FormData();
                tem.parameter = parameter;
                tem.categoryid = categoryId;
                tem.categoryname = categoryName;
                tem.standard = standard;
                tem.typeid = typeId;
                tem.typename = typeName;
                testingCapabilitySet.add(tem);
            }

        }

        List<FormData> testingCapabilityList = new ArrayList<>(testingCapabilitySet);

        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建工作表
        Sheet sheet = workbook.createSheet("检验检测能力申请表");
        // 创建单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 11));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 7));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 9, 9));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 11, 11));

        //创建标题行
        Row headerRow0 = sheet.createRow(0);
        Row headerRow1 = sheet.createRow(1);
        Row headerRow2 = sheet.createRow(2);
        headerRow0.createCell(0).setCellValue("检验检测能力申请表");
        headerRow1.createCell(0).setCellValue("大类序号");
        headerRow1.createCell(1).setCellValue("大类名称");
        headerRow1.createCell(2).setCellValue("类别序号");
        headerRow1.createCell(3).setCellValue("类别(产品/项目/参数)");
        headerRow1.createCell(4).setCellValue("产品/项目/参数");
        headerRow2.createCell(4).setCellValue("序号");
        headerRow2.createCell(5).setCellValue("名称");
        headerRow1.createCell(6).setCellValue("依据的标准（方法）");
        headerRow2.createCell(6).setCellValue("名称");
        headerRow2.createCell(7).setCellValue("编号（含年号）");
        headerRow1.createCell(8).setCellValue("限制范围");
        headerRow1.createCell(9).setCellValue("说明");
        headerRow1.createCell(10).setCellValue("授权签字人姓名");
        headerRow1.createCell(11).setCellValue("身份证号");

        // 填充数据
        int rowNum = 3;
        for (FormData tem : testingCapabilityList) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(NumberChineseFormatter.format(tem.categoryid,false,false));
            row.createCell(1).setCellValue(tem.categoryname);
            row.createCell(2).setCellValue(tem.typeid);
            row.createCell(3).setCellValue(tem.typename);
            row.createCell(4).setCellValue(tem.standard.getId() + "." + tem.parameter.getId());
            row.createCell(5).setCellValue(tem.parameter.getName());
            row.createCell(6).setCellValue(tem.standard.getName());
            row.createCell(7).setCellValue(tem.standard.getIid());
            row.createCell(8).setCellValue("");
            row.createCell(9).setCellValue("");
            row.createCell(10).setCellValue("");
            row.createCell(11).setCellValue("");
        }

        // 绘制边框
        for (Row row : sheet) {
            for (Cell cell : row) {
                cell.setCellStyle(style);
            }
        }
        setCellBorder(sheet, new CellRangeAddress(0, 0, 0, 11));
        for (int i = 0; i <= 11; ++i) {
            setCellBorder(sheet, new CellRangeAddress(1, 2, i, i));
        }

        // 保存Excel文件
        String uuid = UUID.randomUUID().toString();
        String newFilename = uuid + ".xlsx";
        File filePath = new File("resources/excels", newFilename);
        if (!filePath.getParentFile().exists()) {
            filePath.getParentFile().mkdirs();
        }

        // 保存Excel文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return newFilename;
    }

    public String generateExcel3(Project project) {
        Set<FormData> testingCapabilitySet = new HashSet<>();

        Workbook workbook;
        Sheet sheet;
        for (Standard standard : project.getStandards()) {
            Integer categoryId = standard.getCategoryId();
            Integer typeId = standard.getTypeId();
            String categoryName = categoryService.getCategoryNameById(categoryId);
            String typeName = typeService.getTypeNameById(typeId);

            for (Parameter parameter : standard.getParameterList()) {
                for (Equipment equipment : parameter.getEquipmentList()) {
                    FormData tem = new FormData();
                    tem.parameter = parameter;
                    tem.categoryid = categoryId;
                    tem.categoryname = categoryName;
                    tem.standard = standard;
                    tem.typeid = typeId;
                    tem.typename = typeName;
                    tem.equipment = equipment;
                    testingCapabilitySet.add(tem);
                }

            }
        }

        List<FormData> testingCapabilityList = new ArrayList<>(testingCapabilitySet);

        // 创建工作簿
        workbook = new XSSFWorkbook();
        // 创建工作表
        sheet = workbook.createSheet("仪器设备（标准物质）配置表");
        // 创建单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());


        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 9));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 11, 11));
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 12, 12));

        //创建标题行
        Row headerRow0 = sheet.createRow(0);
        Row headerRow1 = sheet.createRow(1);
        Row headerRow2 = sheet.createRow(2);
        headerRow0.createCell(0).setCellValue("仪器设备（标准物质）配置表");
        headerRow1.createCell(0).setCellValue("序号");
        headerRow1.createCell(1).setCellValue("类别(产品/项目/参数)");
        headerRow1.createCell(2).setCellValue("产品/项目/参数");
        headerRow2.createCell(2).setCellValue("序号");
        headerRow2.createCell(3).setCellValue("名称");
        headerRow1.createCell(4).setCellValue("依据的标准（方法）");
        headerRow2.createCell(4).setCellValue("名称");
        headerRow2.createCell(5).setCellValue("编号（含年号）");
        headerRow1.createCell(6).setCellValue("仪器设备（标准物质）");
        headerRow2.createCell(6).setCellValue("编号");
        headerRow2.createCell(7).setCellValue("名称");
        headerRow2.createCell(8).setCellValue("型号/规格/等级");
        headerRow2.createCell(9).setCellValue("测量范围");
        headerRow1.createCell(10).setCellValue("溯源方式");
        headerRow1.createCell(11).setCellValue("有效日期");
        headerRow1.createCell(12).setCellValue("确认结果");


        // 填充数据
        int rowNum = 3;
        for (FormData tem : testingCapabilityList) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(tem.typeid);
            row.createCell(1).setCellValue(tem.typename);
            row.createCell(2).setCellValue(tem.standard.getId() + "." + tem.parameter.getId());
            row.createCell(3).setCellValue(tem.parameter.getName());
            row.createCell(4).setCellValue(tem.standard.getName());
            row.createCell(5).setCellValue(tem.standard.getIid());
            row.createCell(6).setCellValue(tem.equipment.getEquipNo());
            row.createCell(7).setCellValue(tem.equipment.getName());
            row.createCell(8).setCellValue(tem.equipment.getLevel());
            row.createCell(9).setCellValue(tem.equipment.getScope());
            row.createCell(10).setCellValue(tem.equipment.getTraceMethod());
            row.createCell(11).setCellValue(tem.equipment.getValidDate());
            row.createCell(12).setCellValue("");
        }


        for (Row row : sheet) {
            for (Cell cell : row) {
                cell.setCellStyle(style);
            }
        }

        setCellBorder(sheet, new CellRangeAddress(0, 0, 0, 12));
        for (int i = 0; i <= 12; ++i) {
            setCellBorder(sheet, new CellRangeAddress(1, 2, i, i));
        }

        // 保存Excel文件
        String uuid = UUID.randomUUID().toString();
        String newFilename = uuid + ".xlsx";
        File filePath = new File("resources/excels", newFilename);
        if (!filePath.getParentFile().exists()) {
            filePath.getParentFile().mkdirs();
        }

        // 保存Excel文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return newFilename;
    }
}
